using System;
using System.Collections.Generic;
using System.Text;
using Csla;
using System.Data.Odbc;
using MinaspetroLibrary.Configuracoes;
using System.Data;
using Csla.Data;

namespace MinaspetroLibrary.RevistaPack
{
    public enum TipoLista
    {
        Mes,
        Ano
    }
    [Serializable()]
    public class RevistaDatasNameValueList : NameValueListBase<int, string>
    {
        #region Factory Methods
        private RevistaDatasNameValueList()
        { /* require use of factory method */ }

        public static RevistaDatasNameValueList GetRevistaDatasNameValueList(TipoLista tipo)
        {
            return DataPortal.Fetch<RevistaDatasNameValueList>(new FilterCriteria(tipo));
        }

        public static RevistaDatasNameValueList GetRevistaDatasNameValueList(TipoLista tipo, string inicial)
        {
            return DataPortal.Fetch<RevistaDatasNameValueList>(new FilterCriteria(tipo, inicial));
        }
        #endregion //Factory Methods

        #region Data Access
        class FilterCriteria
        {
            public string Inicial = string.Empty;
            public TipoLista Tipo;

            public FilterCriteria(TipoLista tipo)
            {
                this.Tipo = tipo;
            }

            public FilterCriteria(TipoLista tipo, string inicial)
            {
                this.Tipo = tipo;
                this.Inicial = inicial;
            }
        }

        private void DataPortal_Fetch(FilterCriteria criteria)
        {
            RaiseListChangedEvents = false;

            using (OdbcConnection cn = new OdbcConnection(ConfigDatabase.MinaspetroConnection))
            using (OdbcCommand cm = cn.CreateCommand())
            {
                IsReadOnly = false;
                cn.Open();

                if (criteria.Inicial != string.Empty)
                    this.Add(new NameValuePair(0, criteria.Inicial));

                if (criteria.Tipo == TipoLista.Mes)
                {
                    cm.CommandType = CommandType.Text;
                    cm.CommandText = "SELECT distinct month(data) Mes from revistatb order by Mes";


                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                            this.Add(new NameValuePair(Convert.ToInt32(dr["Mes"].ToString()), dr["Mes"].ToString()));
                    }
                }
                else
                {
                    cm.CommandType = CommandType.Text;
                    cm.CommandText = "SELECT distinct year(data) Ano from revistatb order by Ano";


                    using (SafeDataReader dr = new SafeDataReader(cm.ExecuteReader()))
                    {
                        while (dr.Read())
                            this.Add(new NameValuePair(Convert.ToInt32(dr["Ano"].ToString()), dr["Ano"].ToString()));
                    }
                }
                IsReadOnly = true;
            }//using

            RaiseListChangedEvents = true;
        }
        #endregion //Data Access
    }
}
